﻿using EIP.Common.Extension;
using Microsoft.AspNetCore.Http;
using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace EIP.Common.Office
{
    /// <summary>
    /// Excel
    /// </summary>
    public static class EpplusOfficeUtil
    {
        /// <summary>
        /// Excel文件 Content-Type
        /// </summary>
        private const string Excel = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

        /// <summary>
        /// Excel导出
        /// </summary>
        /// <param name="keyValuePairs">字典表【名称，数据】</param>
        /// <param name="sWebRootFolder">网站根文件夹</param>
        /// <param name="tuple"></param>
        public static void Export(Dictionary<string, DataTable> keyValuePairs, string sWebRootFolder, out Tuple<string, string> tuple)
        {
            if (string.IsNullOrWhiteSpace(sWebRootFolder))
                tuple = Tuple.Create("", Excel);
            string sFileName = Guid.NewGuid().ToString() + ".xlsx";
            var path = Path.Combine(sWebRootFolder, sFileName);
            if (!Directory.Exists(sWebRootFolder))
            {
                Directory.CreateDirectory(sWebRootFolder);
            }
            FileInfo file = new FileInfo(path);
            using (ExcelPackage package = new ExcelPackage(file))
            {
                foreach (var item in keyValuePairs)
                {
                    string worksheetTitle = item.Key; //表名称
                    var dt = item.Value; //数据表

                    // 添加worksheet
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(worksheetTitle);
                    //添加表头
                    int column = 1;
                    foreach (DataColumn dc in dt.Columns)
                    {
                        worksheet.Cells[1, column].Value = dc.ColumnName;
                        worksheet.Cells[1, column].Style.Font.Bold = true;//字体为粗体
                        worksheet.Cells[1, column].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;//水平居中
                        worksheet.Cells[1, column].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;//设置样式类型
                        worksheet.Cells[1, column].Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(159, 197, 232));//设置单元格背景色
                        column++;
                    }
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            //添加值
                            worksheet.Cells[i + 2, j + 1].Value = dt.Rows[i][j].ToString();
                        }
                    }
                    //自动列宽
                    worksheet.Cells.AutoFitColumns();
                }
                package.Save();
            }
            tuple = Tuple.Create(sFileName, Excel);
        }

        /// <summary>
        /// Excel导入
        /// </summary>
        /// <param name="excelFile">Excel文件</param>
        /// <param name="sWebRootFolder">文件存储路径</param>
        ///  <param name="content">显示内容</param>
        /// <param name="isShow">是否显示内容</param>
        public static void Import(IFormFile excelFile, string sWebRootFolder, out string content, bool isShow = false)
        {
            if (string.IsNullOrWhiteSpace(sWebRootFolder))
                content = string.Empty;

            string sFileName = $"{DateTime.Now.ToString("yyyyMMddHHmmssfff")}-{Guid.NewGuid()}.xlsx";
            FileInfo file = new FileInfo(Path.Combine(sWebRootFolder, sFileName));

            using (FileStream fs = new FileStream(file.ToString(), FileMode.Create))
            {
                excelFile.CopyToAsync(fs);
                fs.Flush();
            }

            if (isShow)
            {
                //导出单个工作表sheet
                using (ExcelPackage package = new ExcelPackage(file))
                {
                    StringBuilder sb = new StringBuilder();
                    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
                    int rowCount = worksheet.Dimension.Rows;
                    int colCount = worksheet.Dimension.Columns;
                    for (int row = 1; row <= rowCount; row++)
                    {
                        for (int col = 1; col <= colCount; col++)
                        {
                            sb.Append(worksheet.Cells[row, col].Value + "\t");
                        }
                        sb.Append(Environment.NewLine);
                    }
                    content = sb.ToString();
                }
            }
            else
            {
                content = string.Empty;
            }
        }
    }
}
